package cn.xiaobai.utils;

import cn.xiaobai.enums.InitSQL;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Component;

import java.util.*;

/**
 * 数据库工具
 *
 * @Author yangdaji
 * @Date 2024/3/24
 * @PackageName cn.xiaobai.utils
 * @ClassName DBUtils
 */
@Component
public class DBUtils {

    @Autowired
    private JdbcTemplate jdbcTemplate;

    /**
     * 查询指定库的所有表
     *
     * @param database 库名
     * @return
     */
    public List<String> showTables(String database) {
        String sql = String.format(InitSQL.SHOW_TABLES.getSql(), database);
        return jdbcTemplate.queryForList(sql, String.class);
    }

    /**
     * 查询指定库指定表的所有字段
     *
     * @param database  库名
     * @param tableName 表名
     * @return
     */
    public List<String> showTableColum(String database, String tableName) {
        String sql = String.format(InitSQL.SHOW_TABLE_COLUM.getSql(), database, tableName);
        return jdbcTemplate.queryForList(sql, String.class);
    }

    // 创建表

    /**
     * 创建表
     *
     * @param tableName 表名
     * @param columns   字段
     */
    public void createTable(String tableName, List<String> columns) {
        StringBuilder createSql = new StringBuilder("create table if not exists ");
        createSql.append("`").append(tableName).append("`").append("(");
        for (int i = 0; i < columns.size(); i++) {
            createSql.append("`").append(columns.get(i)).append("`");
            // 暂时全部认为字符串, 数据类型解析比较麻烦, 耗时间
            createSql.append(" varchar(255)");
            createSql.append(",");
        }
        createSql.deleteCharAt(createSql.length() - 1).append(");");
        jdbcTemplate.execute(createSql.toString());
    }


    /**
     * 插入数据
     *
     * @param tableName  表名
     * @param columns    字段
     * @param insertData 插入数据
     */
    public void insertData(String tableName, List<String> columns, List<List<Object>> insertData) {
        StringBuilder insertSql = new StringBuilder("insert into ");
        insertSql.append("`").append(tableName).append("` (");
        // 字段
        for (String column : columns) {
            insertSql.append("`").append(column).append("`").append(",");
        }
        insertSql.deleteCharAt(insertSql.length() - 1).append(") ");

        insertSql.append("values");
        // data
        for (List<Object> row : insertData) {
            insertSql.append("(");
            for (int i = 0; i < columns.size(); i++) {
                insertSql.append("'");
                // 空数据补 null
                try {
                    insertSql.append(row.get(i));
                } catch (IndexOutOfBoundsException e) {
                    insertSql.append("null");
                }
                insertSql.append("'").append(",");
            }
            insertSql.deleteCharAt(insertSql.length() - 1).append(")").append(",");
        }
        insertSql.deleteCharAt(insertSql.length() - 1).append(";");
        jdbcTemplate.execute(insertSql.toString());
    }

    public Map<String, Object> queryDataByNameOrAll(String tableName) {
        Map<String, Object> data = new HashMap<>();
        StringBuilder querySql = new StringBuilder("select * from ");
        querySql.append("`").append(tableName).append("`").append(";");
        List<Map<String, Object>> maps = jdbcTemplate.queryForList(querySql.toString());
        List<String> colum = showTableColum("excelTool", tableName);

        // head
        data.put("head", colum);
        List<Collection<Object>> list = maps.stream().map(Map::values).toList();
        data.put("body", list);
        return data;
    }

    // 根据表名查询字段
    // 查询数据
}
